﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using MediaLibraryManagement.Common;
using MediaLibraryManagement.Entity;

namespace MediaLibraryManagement.DAL
{
    public class MovieDAL : BaseDAL
    {
        #region Properties Implementation

        protected override string TableName { get { return DatabaseTableName.Movies; } }

        #endregion

        #region Public Methods

        public int Insert(Movie movie)
        {
            int id = 0;
            try
            {
                OpenConnection();
                string commandstring = string.Format("INSERT INTO {0} (Title, Storyline, Genre, Length, Language, Director,ScriptWriters, ProductionCompany, Rate, RatingCount, ReleaseDate, IMDB, Image ) "
                                                            + "OUTPUT INSERTED.ID "
                                                            + "VALUES (@Title, @Storyline, @Genre, @Length, @Language, @Director,@ScriptWriters, @ProductionCompany, @Rate, @RatingCount, @ReleaseDate, @IMDB, @Image) ",
                                                            TableName);
                using (SqlCommand command = new SqlCommand(commandstring, Connection))
                {
                    command.Parameters.AddWithValue("@Title", movie.Title);
                    command.Parameters.AddWithValue("@Storyline", movie.Storyline);
                    command.Parameters.AddWithValue("@Genre", movie.Genre);
                    command.Parameters.AddWithValue("@Length", movie.Length);
                    command.Parameters.AddWithValue("@Language", movie.Language);
                    command.Parameters.AddWithValue("@Director", movie.Director);
                    command.Parameters.AddWithValue("@ScriptWriters", movie.ScriptWriters);
                    command.Parameters.AddWithValue("@ProductionCompany", movie.ProductionCompany);
                    command.Parameters.AddWithValue("@Rate", movie.Rate);
                    command.Parameters.AddWithValue("@RatingCount", movie.RatingCount);
                    command.Parameters.AddWithValue("@ReleaseDate", movie.ReleaseDate);
                    command.Parameters.AddWithValue("@IMDB", movie.IMDB);
                    command.Parameters.AddWithValue("@Image", Utilities.ImageToByteArray(movie.Image));
                    id = (int)command.ExecuteScalar();
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
            return id;
        }

        public void Update(Movie movie)
        {
            try
            {
                OpenConnection();
                string commandString = string.Format("UPDATE {0} SET "
                                                        + "Title = @Title, Storyline = @Storyline, Genre = @Genre, Length = @Length, Language = @Language, Director = @Director, ScriptWriters = @ScriptWriters, ProductionCompany = @ProductionCompany, Rate = @Rate, RatingCount = @RatingCount, ReleaseDate = @ReleaseDate, IMDB = @IMDB, Image = @Image "
                                                        + "WHERE ID = {1}"
                                                    , TableName
                                                    , movie.ID);
                using (SqlCommand command = new SqlCommand(commandString, Connection))
                {
                    command.Parameters.AddWithValue("@Title", movie.Title);
                    command.Parameters.AddWithValue("@Storyline", movie.Storyline);
                    command.Parameters.AddWithValue("@Genre", movie.Genre);
                    command.Parameters.AddWithValue("@Length", movie.Length);
                    command.Parameters.AddWithValue("@Language", movie.Language);
                    command.Parameters.AddWithValue("@Director", movie.Director);
                    command.Parameters.AddWithValue("@ScriptWriters", movie.ScriptWriters);
                    command.Parameters.AddWithValue("@ProductionCompany", movie.ProductionCompany);
                    command.Parameters.AddWithValue("@Rate", movie.Rate);
                    command.Parameters.AddWithValue("@RatingCount", movie.RatingCount);
                    command.Parameters.AddWithValue("@ReleaseDate", movie.ReleaseDate);
                    command.Parameters.AddWithValue("@IMDB", movie.IMDB);
                    command.Parameters.AddWithValue("@Image", Utilities.ImageToByteArray(movie.Image));
                    command.ExecuteNonQuery();
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
        }

        public List<Movie> Search(string title, string genre, int year)
        {
            List<Movie> movies = new List<Movie>();
            try
            {
                OpenConnection();
                string commandString = string.Format("SELECT * FROM {0} "
                                                        + "WHERE Title LIKE '%{1}%' "
                                                        + "AND Genre LIKE '%{2}%' "
                                                        , TableName, title, genre);
                if (year > 0)
                {
                    commandString += string.Format("AND YEAR(ReleaseDate) = {0}", year);
                }
                using (SqlCommand command = new SqlCommand(commandString, Connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Movie movie = (Movie)Fetch(reader);
                            movies.Add(movie);
                        }
                        reader.Close();
                    }
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
            return movies;
        }

        #endregion

        #region Protected Methods

        protected override IEntity Fetch(IDataReader reader)
        {
            Movie movie = new Movie();
            movie.ID = (Int32)(reader["ID"]);
            movie.Title = reader["Title"].ToString();
            movie.Storyline = reader["Storyline"].ToString();
            movie.Genre = reader["Genre"].ToString();
            movie.Length = (Int32)reader["Length"];
            movie.Language = reader["Language"].ToString();
            movie.Director = reader["Director"].ToString();
            movie.ScriptWriters = reader["ScriptWriters"].ToString();
            movie.ProductionCompany = reader["ProductionCompany"].ToString();
            movie.Rate = float.Parse(reader["Rate"].ToString());
            movie.RatingCount = (int)reader["RatingCount"];
            movie.ReleaseDate = (DateTime)reader["ReleaseDate"];
            movie.IMDB = reader["IMDB"].ToString();
            movie.Image = Utilities.ByteArrayToImage((byte[])reader["Image"]);
            return movie;
        }

        #endregion
    }
}